package se.mbaeumer.fxtodo.db;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import se.mbaeumer.fxtodo.core.model.Context;
import se.mbaeumer.fxtodo.core.model.EnumICSInfoType;
import se.mbaeumer.fxtodo.core.model.EnumPriority;
import se.mbaeumer.fxtodo.core.model.EnumTaskStatus;
import se.mbaeumer.fxtodo.core.model.ICSInfo;
import se.mbaeumer.fxtodo.core.model.Task;
import se.mbaeumer.fxtodo.core.model.TaskStatus;
import se.mbaeumer.fxtodo.core.model.TaskType;
import se.mbaeumer.fxtodo.core.util.DateUtils;
import se.mbaeumer.fxtodo.core.util.constants.FilterConstants;
import se.mbaeumer.fxtodo.core.util.constants.ValueConstants;

public class DatabaseHandler {
	private static final DatabaseHandler INSTANCE = new DatabaseHandler();

	private DatabaseHandler() {
	};

	public static DatabaseHandler getInstance() {
		return INSTANCE;
	}

	private Connection connection;

	/**
	 * Connects to the database
	 * 
	 * @return
	 */
	public int connect() {
		File f = new File("db/fxtodo");
		try {
			System.out.println("CONNECTING TO: " + f.getCanonicalPath());
		} catch (IOException e1) {
			e1.printStackTrace();
		}

		try {
			try {
				try {
					Class.forName("org.hsqldb.jdbcDriver").newInstance();
				} catch (InstantiationException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}

			this.connection = DriverManager.getConnection(
					"jdbc:hsqldb:file:db/fxtodo", "SA", "");
		} catch (SQLException e) {
			e.printStackTrace();
		}

		if (this.connection == null) {
			return 1;
		}

		return 0;
	}

	/**
	 * Disconnects from the database
	 */
	public void disconnect() {
		try {
			this.connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Gets all contexts from the database
	 */
	public List<Context> getContexts() {
		List<Context> contexts = new ArrayList<Context>();
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT * FROM context";
		try {
			Statement stmt = this.connection.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Context c = new Context.ContextBuilder().id(rs.getInt("id"))
						.title(rs.getString("title"))
						.description(rs.getString("description"))
						.created(rs.getTimestamp("created")).build();
				contexts.add(c);

			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return contexts;
	}

	public void importContext(Context context) {
		if (this.connection == null) {
			this.connect();
		}

		String sql = "INSERT INTO context VALUES(?, ?, ?, ?)";

		PreparedStatement stmt = null;
		Timestamp ts = new Timestamp(context.getCreated().getTime());
		try {
			stmt = this.connection.prepareStatement(sql);

			stmt.setInt(1, context.getId());
			stmt.setString(2, context.getTitle());
			stmt.setString(3,
					context.getDescription() == ValueConstants.VALUE_N_A ? null
							: context.getDescription());
			stmt.setTimestamp(4, ts);
			stmt.executeUpdate();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * Creates a new context
	 * 
	 * @param context
	 * @throws SQLException
	 */
	public void createContext(Context context) throws SQLException {
		if (this.connection == null) {
			this.connect();
		}

		String sql = "INSERT INTO context " + "VALUES(DEFAULT, ?, ?, ?)";

		Timestamp ts = new Timestamp(new Date().getTime());
		PreparedStatement stmt = null;
		stmt = this.connection.prepareStatement(sql);
		stmt.setString(1, context.getTitle());
		stmt.setString(2,
				context.getDescription() == ValueConstants.VALUE_N_A ? null
						: context.getDescription());
		stmt.setTimestamp(3, ts);
		System.out.println("context created: " + ts);
		stmt.executeUpdate();
		stmt.close();
	}
	
	public int importTaskFromICS(Task task, int priorityId){
		int taskId = -1;
		
		if (this.connection == null) {
			this.connect();
		}

		int statusId = this.getStatusIDByTitle(EnumTaskStatus.Open.toString());
		Timestamp tsDueDate = new Timestamp(task.getDueDate().getTime());
		
		String sql = "INSERT INTO task "
				+ "VALUES(DEFAULT, ?, ?, DEFAULT, ?, ?, DEFAULT, DEFAULT, ?)";
		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			stmt.setString(1, task.getTitle());
			stmt.setString(2,
					task.getDescription() == ValueConstants.VALUE_N_A ? null
							: task.getDescription());
			stmt.setTimestamp(3, tsDueDate);
			stmt.setInt(4, statusId);
			stmt.setInt(5, priorityId);
			stmt.executeUpdate();
			
			ResultSet rs = stmt.getGeneratedKeys();
			while (rs.next()){
				taskId = rs.getInt(1);
			}
			
			stmt.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return taskId;
	}
	
	public void importICSInfoFromXML(ICSInfo icsInfo) throws SQLException{
		if (this.connection == null) {
			this.connect();
		}

		Timestamp tsCreated = new Timestamp(icsInfo.getCreated().getTime());
		Timestamp tsStart = new Timestamp(icsInfo.getStartTime().getTime());
		Timestamp tsEnd = new Timestamp(icsInfo.getEndTime().getTime());
		
		String sql = "INSERT INTO ics_info " +
				"VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		PreparedStatement stmt = null;
		stmt = this.connection.prepareStatement(sql);
		stmt.setInt(1, icsInfo.getId());
		stmt.setString(2, icsInfo.getsummary());
		stmt.setString(3,
				icsInfo.getDescription() == ValueConstants.VALUE_N_A ? null
						: icsInfo.getDescription());
		stmt.setTimestamp(4, tsStart);
		stmt.setTimestamp(5, tsEnd);
		stmt.setInt(6, new Integer(this.getPriorityIDByTitle(icsInfo.getPriority().toString())));
		stmt.setInt(7, new Integer(this.getStatusIDByTitle(icsInfo.getStatus().toString())));
		stmt.setInt(8, this.getICSInfoTypeIDByName(icsInfo.getICSInfoType().toString()));
		stmt.setInt(9, icsInfo.getTaskId());
		stmt.setTimestamp(10, tsCreated);
		stmt.executeUpdate();
		stmt.close();
	
	}
	
	public void importTaskFromXML(Task task){
		if (this.connection == null) {
			this.connect();
		}

		Timestamp tsCreated = new Timestamp(task.getCreated().getTime());
		Timestamp tsDueDate = new Timestamp(task.getDueDate().getTime());
		/*( "ID", "TITLE", "DESCRIPTION", 
				"CREATED", "DUEDATE", "STATUSID", 
				"TASKTYPEID", "CONTEXTID", "PRIORITYID" )
		 */
		// id
		// title
		// description
		// due date
		// status
		// context
		// created
		String sql = "INSERT INTO task "
				+ "VALUES(?, ?, ?, ?, ?, ?, DEFAULT, ";
		
		if (task.getTaskContext() == null){
			sql += " DEFAULT,";
		}else{
			sql += " ?,";
		}
		
		sql += " ?)";
		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setInt(1, task.getId());
			stmt.setString(2, task.getTitle());
			stmt.setString(3,
					task.getDescription() == ValueConstants.VALUE_N_A ? null
							: task.getDescription());
			stmt.setTimestamp(4, tsCreated);
			stmt.setTimestamp(5, tsDueDate);
			stmt.setInt(6, this.getStatusIDByTitle(task.getStatus().toString()));
			if (task.getTaskContext() != null){
				stmt.setInt(7, task.getTaskContext().getId());
				stmt.setInt(8, this.getPriorityIDByTitle(task.getPriority().toString()));
			}else{
				stmt.setInt(7, this.getPriorityIDByTitle(task.getPriority().toString()));
			}
			
			
			
			/*if (contextId != -1) {
				stmt.setInt(5, contextId);
				stmt.setInt(6, priorityId);
			} else {
				stmt.setInt(5, priorityId);
			}*/
			stmt.executeUpdate();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Creates a new task
	 * 
	 * @param task
	 * @param contextId
	 * @param statusId
	 */
	public void createTask(Task task, int contextId, int statusId,
			int priorityId) {
		if (this.connection == null) {
			this.connect();
		}

		Timestamp tsDueDate = new Timestamp(task.getDueDate().getTime());
		Timestamp tsCreated = new Timestamp(new Date().getTime());
		String sql = "INSERT INTO task "
				+ "VALUES(DEFAULT, ?, ?, ?, ?, ?, DEFAULT ";
		if (contextId != -1) {
			sql += ", ?";
		} else {
			sql += ", DEFAULT";
		}
		sql += ", ?";
		sql += ")";

		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, task.getTitle());
			stmt.setString(2,
					task.getDescription() == ValueConstants.VALUE_N_A ? null
							: task.getDescription());
			stmt.setTimestamp(3, tsDueDate);
			stmt.setTimestamp(4, tsCreated);
			stmt.setInt(5, statusId);
			if (contextId != -1) {
				stmt.setInt(6, contextId);
				stmt.setInt(7, priorityId);
			} else {
				stmt.setInt(6, priorityId);
			}
			stmt.executeUpdate();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Updates an existing task
	 * 
	 * @param task
	 * @param contextId
	 * @param statusId
	 */
	public void updateTask(Task task, int contextId, int statusId,
			int priorityId) {
		if (this.connection == null) {
			this.connect();
		}
		Timestamp ts = new Timestamp(task.getDueDate().getTime());
		String sql = "UPDATE task SET title=?" + ", description=?"
				+ ", dueDate=?" + ", statusid=?" + ", priorityid=?";

		if (contextId != -1) {
			sql += ", contextid=?";
		}
		sql += " WHERE id=?";
		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, task.getTitle());
			stmt.setString(2,
					task.getDescription() == ValueConstants.VALUE_N_A ? null
							: task.getDescription());
			stmt.setTimestamp(3, ts);
			stmt.setInt(4, statusId);
			stmt.setInt(5, priorityId);
			if (contextId != -1) {
				stmt.setInt(6, contextId);
				stmt.setInt(7, task.getId());
			} else {
				stmt.setInt(6, task.getId());
			}
			stmt.executeUpdate();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void moveTask(Task task, int contextId) {
		if (this.connection == null) {
			this.connect();
		}

		String sql = "UPDATE task SET contextid=";

		if (contextId != -1) {
			sql += "?";
		} else {
			sql += "NULL";
		}

		sql += " WHERE id=?";
		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			if (contextId != -1) {
				stmt.setInt(1, contextId);
				stmt.setInt(2, task.getId());
			} else {
				stmt.setInt(1, task.getId());
			}

			stmt.executeUpdate();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void deleteTask(int taskId) {
		if (this.connection == null) {
			this.connect();
		}
		String sql = "DELETE FROM task WHERE id=?";

		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setInt(1, taskId);
			stmt.executeUpdate();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void deleteTasksFromContext(int contextId) {
		if (this.connection == null) {
			this.connect();
		}

		String sql = "DELETE FROM task t";

		if (contextId != -1) {
			sql += " WHERE t.contextid=?";
		}
		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			if (contextId != -1) {
				stmt.setInt(1, contextId);
			}
			stmt.executeUpdate();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Updates a given context
	 * 
	 * @param context
	 */
	public void updateContext(Context context) throws SQLException {
		if (this.connection == null) {
			this.connect();
		}

		String sql = "UPDATE context SET title=?" + ", description=?"
				+ " WHERE id=?";
		PreparedStatement stmt = this.connection.prepareStatement(sql);
		stmt.setString(1, context.getTitle());
		stmt.setString(2,
				context.getDescription() == ValueConstants.VALUE_N_A ? null
						: context.getDescription());
		stmt.setInt(3, context.getId());
		stmt.executeUpdate();
		stmt.close();
	}

	public void deleteContexts(int contextId) {
		if (this.connection == null) {
			this.connect();
		}
		String sql = "DELETE FROM context";
		
		if (contextId != -1){
			sql += " WHERE id=?";
		}
		
		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			if (contextId != -1){
				stmt.setInt(1, contextId);
			}
			stmt.executeUpdate();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Gets all task statuses
	 * 
	 * @return
	 */
	public List<TaskStatus> getTaskStatuses() {
		List<TaskStatus> statuses = new ArrayList<TaskStatus>();
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT * FROM taskstatus";
		try {
			Statement stmt = this.connection.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				TaskStatus ts = new TaskStatus();
				ts.setId(rs.getInt("id"));
				ts.setTitle(rs.getString("title"));
				ts.setDescription(rs.getString("description"));

				statuses.add(ts);

			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return statuses;

	}

	public List<TaskType> getTaskTypes() {
		List<TaskType> types = new ArrayList<TaskType>();
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT * FROM tasktype";
		try {
			Statement stmt = this.connection.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				TaskType tt = new TaskType();
				tt.setId(rs.getInt("id"));
				tt.setTitle(rs.getString("title"));
				tt.setDescription(rs.getString("description"));

				types.add(tt);

			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return types;
	}

	public List<Task> getTasks(int contextId, String period, int statusId,
			int priorityId) {
		List<Task> tasks = new ArrayList<Task>();
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT * FROM task ";
		if (contextId == -1) { // context == null
			sql += "WHERE contextid IS NULL ";
		} else if (contextId == -2) { // all contexts (incl. null)
			sql += "";
		} else {// defined context
			sql += "WHERE contextid = " + contextId;
		}

		if (statusId != -1) {
			if (contextId != -2) { // if WHERE-clause contains context
				sql += " AND statusid = " + statusId;
			} else {
				sql += " WHERE statusid = " + statusId;
			}
		}

		if (!period.equals(FilterConstants.FILTER_NONE)) {
			List<String> dateStrings = this.getDates(period);
			if ((contextId != -2) || (statusId != -1)) { // if WHERE-clause
															// contains context
															// or status
				sql += " AND ";
			} else {
				sql += "WHERE ";
			}
			sql += "(dueDate >= CAST('" + dateStrings.get(0) + "' AS TIMESTAMP) "
					+ "AND dueDate < CAST('" + dateStrings.get(1)
					+ "' AS TIMESTAMP))";
		}

		if (priorityId != -1) {
			if ((contextId != -2) || (statusId != -1)
					|| (!period.equals(FilterConstants.FILTER_NONE))) { // if
				sql += " AND ";
			} else {
				sql += "WHERE ";
			}
			sql += " priorityid= " + priorityId;
		}

		//System.out.println(sql);
		try {
			Statement stmt = this.connection.createStatement();
			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				Task t = new Task.TaskBuilder()
						.id(rs.getInt("id"))
						.title(rs.getString("title"))
						.description(rs.getString("description"))
						.created(rs.getTimestamp("created"))
						.dueDate(rs.getTimestamp("dueDate"))
						.taskType(this.getTaskTypeByID(rs.getInt("taskTypeid")))
						.status(this.getTaskStatusByID(rs.getInt("statusid")))
						.taskContext(
								this.getContextByID(rs.getInt("contextid")))
						.priority(this.getPriorityById(rs.getInt("priorityid")))
						.build();
				tasks.add(t);
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tasks;
	}

	private List<String> getDates(String period) {
		Date from = null;
		Date to = null;
		List<String> dateStrings = new ArrayList<String>();
		String strFrom = null;
		String strTo = null;
		List<Date> dates = new ArrayList<Date>();
		if (period.equals("Today")) {
			dates = DateUtils.getDayFromTo(from, to, false);
		}else if (period.equals("Tomorrow")) {
			dates = DateUtils.getDayFromTo(from, to, true);
		} else if (period.equals("Weekly")) {
			dates = DateUtils.getWeekFromTo(from, to);
		} else if (period.equals("Monthly")) {
			dates = DateUtils.getMonthFromTo(from, to);
		}

		strFrom = DateUtils.formatDate(dates.get(0));
		strTo = DateUtils.formatDate(dates.get(1));
		dateStrings.add(strFrom);
		dateStrings.add(strTo);
		return dateStrings;
	}

	/**
	 * Gets all tasks
	 * 
	 * @return
	 */
	public List<Task> getTasks() {
		List<Task> tasks = new ArrayList<Task>();
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT * FROM task";
		try {
			Statement stmt = this.connection.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Task t = new Task.TaskBuilder()
						.id(rs.getInt("id"))
						.title(rs.getString("title"))
						.description(rs.getString("description"))
						.created(rs.getTimestamp("created"))
						.dueDate(rs.getTimestamp("dueDate"))
						.taskType(this.getTaskTypeByID(rs.getInt("taskTypeid")))
						.status(this.getTaskStatusByID(rs.getInt("statusid")))
						.taskContext(
								this.getContextByID(rs.getInt("contextid")))
						.priority(this.getPriorityById(rs.getInt("priorityid")))
						.build();
				tasks.add(t);
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tasks;
	}

	/**
	 * Gets all tasks with a given context
	 * 
	 * @param contextId
	 *            the id of the context
	 * @return a list of tasks
	 */
	public List<Task> getTasksByContext(int contextId) {
		List<Task> tasks = new ArrayList<Task>();
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT * FROM task WHERE contextid ";
		if (contextId != -1) {
			sql += "=?";
		} else {
			sql += " IS NULL";
		}

		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			if (contextId != -1) {
				stmt.setInt(1, contextId);
			}
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				Task t = new Task.TaskBuilder()
						.id(rs.getInt("id"))
						.title(rs.getString("title"))
						.description(rs.getString("description"))
						.created(rs.getTimestamp("created"))
						.dueDate(rs.getTimestamp("dueDate"))
						.taskType(this.getTaskTypeByID(rs.getInt("taskTypeid")))
						.status(this.getTaskStatusByID(rs.getInt("statusid")))
						.taskContext(
								this.getContextByID(rs.getInt("contextid")))
						.build();
				tasks.add(t);
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tasks;
	}

	public List<Task> getTasksByStatus(int statusId, int contextId) {
		List<Task> tasks = new ArrayList<Task>();
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT * FROM task WHERE statusid=? ";

		if (contextId != -1) {
			sql += "AND contextid=?";
		}

		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setInt(1, statusId);
			ResultSet rs = stmt.executeQuery();
			if (contextId != -1) {
				stmt.setInt(2, contextId);
			}
			while (rs.next()) {
				Task t = new Task.TaskBuilder()
						.id(rs.getInt("id"))
						.title(rs.getString("title"))
						.description(rs.getString("description"))
						.created(rs.getTimestamp("created"))
						.dueDate(rs.getTimestamp("dueDate"))
						.taskType(this.getTaskTypeByID(rs.getInt("taskTypeid")))
						.status(this.getTaskStatusByID(rs.getInt("statusid")))
						.taskContext(
								this.getContextByID(rs.getInt("contextid")))
						.build();
				tasks.add(t);
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tasks;
	}

	/**
	 * Gets a list of tasks within a given time period
	 * 
	 * @param period
	 * @param contextId
	 * @return
	 */
	public List<Task> getTasksByTimePeriod(String period, int contextId) {
		List<Task> tasks = new ArrayList<Task>();
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT * " + "FROM task "
				+ "WHERE dueDate >= CAST(? AS TIMESTAMP) "
				+ "AND dueDate < cast(? AS TIMESTAMP) ";
		if (contextId != -1) {
			sql += "AND contextid=?";
		}
		Date from = new Date();
		Date to = new Date();
		String strFrom = null;
		String strTo = null;
		List<Date> dates = new ArrayList<Date>();

		if (period.equals("Today")) {
			dates = DateUtils.getDayFromTo(from, to, false);
		}else if (period.equals("Tomorrow")) {
			dates = DateUtils.getDayFromTo(from, to, true);
		} else if (period.equals("Weekly")) {
			dates = DateUtils.getWeekFromTo(from, to);
		} else if (period.equals("Monthly")) {
			dates = DateUtils.getMonthFromTo(from, to);
		}

		strFrom = DateUtils.formatDate(dates.get(0));
		strTo = DateUtils.formatDate(dates.get(1));

		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, strFrom);
			stmt.setString(2, strTo);
			if (contextId != -1) {
				stmt.setInt(3, contextId);
			}
			//System.out.println(stmt.getConnection().);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				Task t = new Task.TaskBuilder()
						.id(rs.getInt("id"))
						.title(rs.getString("title"))
						.description(rs.getString("description"))
						.created(rs.getTimestamp("created"))
						.dueDate(rs.getTimestamp("dueDate"))
						.taskType(this.getTaskTypeByID(rs.getInt("taskTypeid")))
						.status(this.getTaskStatusByID(rs.getInt("statusid")))
						.taskContext(
								this.getContextByID(rs.getInt("contextid")))
						.build();
				tasks.add(t);
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return tasks;
	}

	/**
	 * Gets a task type by a given id
	 * 
	 * @param typeID
	 *            the id of the type
	 * @return the task type
	 */
	public TaskType getTaskTypeByID(int typeID) {
		TaskType taskType = null;
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT * FROM tasktype WHERE id=?";
		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setInt(1, typeID);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				taskType = new TaskType();
				taskType.setId(rs.getInt("id"));
				taskType.setTitle(rs.getString("title"));
				taskType.setDescription(rs.getString("description"));
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return taskType;
	}

	/**
	 * Gets a task status by a given id
	 * 
	 * @param statusid
	 *            the id of the status
	 * @return the status
	 */
	public EnumTaskStatus getTaskStatusByID(int statusid) {
		EnumTaskStatus taskStatus = null;
		if (statusid == 0) {
			taskStatus = EnumTaskStatus.Open;
		} else if (statusid == 1) {
			taskStatus = EnumTaskStatus.Ongoing;
		} else if (statusid == 2) {
			taskStatus = EnumTaskStatus.Done;
		} else {
			taskStatus = EnumTaskStatus.Cancelled;
		}
		return taskStatus;
	}

	public void createICSInfo(ICSInfo icsInfo) throws SQLException{
		if (this.connection == null) {
			this.connect();
		}

		String sql = "INSERT INTO ics_info " +
				"VALUES(DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, DEFAULT)";

		Timestamp tsStart = new Timestamp(icsInfo.getStartTime().getTime());
		Timestamp tsEnd = new Timestamp(icsInfo.getEndTime().getTime());

		PreparedStatement stmt = null;
		stmt = this.connection.prepareStatement(sql);
		stmt.setString(1, icsInfo.getsummary());
		stmt.setString(2,
				icsInfo.getDescription() == ValueConstants.VALUE_N_A ? null
						: icsInfo.getDescription());
		stmt.setTimestamp(3, tsStart);
		stmt.setTimestamp(4, tsEnd);
		stmt.setInt(5, new Integer(icsInfo.getPriorityString()));
		stmt.setInt(6, new Integer(icsInfo.getStatusString()));
		stmt.setInt(7, this.getICSInfoTypeIDByName(icsInfo.getICSInfoType().toString()));
		stmt.setInt(8, icsInfo.getTaskId());
		stmt.executeUpdate();
		stmt.close();
	}
	
	public EnumICSInfoType getICSInfoTypeByID(int typeid) {
		EnumICSInfoType icsInfoType = null;
		if (typeid == 0) {
			icsInfoType = EnumICSInfoType.Event;
		} else if (typeid == 1) {
			icsInfoType = EnumICSInfoType.Todo;
		}
		return icsInfoType;
	}
	
	public List<ICSInfo> getICSInfos(){
		List<ICSInfo> icsInfos = new ArrayList<ICSInfo>();

		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT * FROM ics_info";

		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				ICSInfo ics = new ICSInfo();
				ics.setId(rs.getInt("id"));
				ics.setSummary(rs.getString("summary"));
				ics.setDescription(rs.getString("description"));
				ics.setStartTime(rs.getTimestamp("startTime"));
				ics.setEndTime(rs.getTimestamp("endTime"));
				ics.setPriority(this.getPriorityById(rs.getInt("priorityid")));
				ics.setStatus(this.getTaskStatusByID(rs.getInt("statusid")));
				ics.setICSInfoType(this.getICSInfoTypeByID(rs.getInt("typeid")));
				ics.setTaskId(rs.getInt("taskid"));
				ics.setCreated(rs.getTimestamp("created"));
				icsInfos.add(ics);
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return icsInfos;
	}
	
	public ICSInfo getICSInfoByTaskID(int taskId){
		ICSInfo ics = null;
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT * FROM ics_info WHERE taskid=?";

		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setInt(1, taskId);
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				ics = new ICSInfo();
				ics.setId(rs.getInt("id"));
				ics.setSummary(rs.getString("summary"));
				ics.setDescription(rs.getString("description"));
				ics.setStartTime(rs.getTimestamp("startTime"));
				ics.setEndTime(rs.getTimestamp("endTime"));
				ics.setPriority(this.getPriorityById(rs.getInt("priorityid")));
				ics.setStatus(this.getTaskStatusByID(rs.getInt("statusid")));
				ics.setICSInfoType(this.getICSInfoTypeByID(rs.getInt("typeid")));
				ics.setTaskId(rs.getInt("taskid"));
				ics.setCreated(rs.getTimestamp("created"));
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return ics;
	}
	
	public int getICSInfoTypeIDByName(String name){
		int icsInfoTypeid = -1;
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT id FROM ics_info_type WHERE name=?";

		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, name);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				icsInfoTypeid = rs.getInt("id");
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return icsInfoTypeid;

	}

	public EnumICSInfoType getICSInfoTypeByName(String name){
		EnumICSInfoType icsInfoType = null;
		int icsInfoTypeid = -1;
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT * FROM ics_info_type WHERE name=?";

		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, name);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				icsInfoTypeid = rs.getInt("id");
				icsInfoType = this.getICSInfoTypeByID(icsInfoTypeid);
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return icsInfoType;

	}
	
	public void deleteICSInfos(){
		if (this.connection == null) {
			this.connect();
		}
		
		String sql = "DELETE FROM ics_info";
		
		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.executeUpdate();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Gets a context by a given id
	 * 
	 * @param contextid
	 *            the id of the context
	 * @return the context
	 */
	public Context getContextByID(int contextId) {
		Context context = null;
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT * FROM context WHERE id=?";

		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setInt(1, contextId);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				context = new Context.ContextBuilder().id(rs.getInt("id"))
						.title(rs.getString("title"))
						.description(rs.getString("description"))
						.created(rs.getDate("created")).build();
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return context;
	}

	/**
	 * Gets a status id by a given title
	 * 
	 * @param title
	 * @return
	 */
	public int getStatusIDByTitle(String title) {
		int statusid = -1;
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT id FROM taskstatus WHERE title=?";

		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, title);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				statusid = rs.getInt("id");
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return statusid;
	}

	/**
	 * Gets a context id by a given context title
	 * 
	 * @param title
	 * @return
	 */
	public int getContextIDByTitle(String title) {
		int contextid = -1;
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT id FROM context WHERE title=?";

		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, title);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				contextid = rs.getInt("id");
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return contextid;
	}

	public List<String> getPriorityTitles() {
		List<String> priorityTitles = new ArrayList<String>();

		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT title FROM priority";

		try {
			Statement stmt = this.connection.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				priorityTitles.add(rs.getString("title"));
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return priorityTitles;
	}

	public int getPriorityIDByTitle(String title) {
		int priorityId = -1;
		if (this.connection == null) {
			this.connect();
		}

		String sql = "SELECT id FROM priority WHERE title=?";

		try {
			PreparedStatement stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, title);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				priorityId = rs.getInt("id");
			}
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return priorityId;

	}

	public EnumPriority getPriorityById(int priorityId) {
		EnumPriority priority = null;
		if (priorityId == 1) {
			priority = EnumPriority.High;
		} else if (priorityId == 2) {
			priority = EnumPriority.Low;
		} else {
			priority = EnumPriority.Normal;
		}
		return priority;
	}
}